Imports System.Data.OleDb
'Imports System.Data.SqlClient

Public Class DBConnection
    Private StrConn_SVR As String

    Public Function DBConnecting() As Boolean
        Dim MyResult As Boolean = False
        Try
            If MyDBCn.State = ConnectionState.Closed Then
                MyDBCn.Open()
                MyResult = True
            End If
        Catch ex As System.Data.SqlClient.SqlException
            ErrRecording("", "ServerConnection/ServerConnecting", ex.Message)
        End Try
        Return MyResult
    End Function

    Public Function ExecuteProcedureDBSVR(ByVal _spName As String) As DataSet
        Dim ds As New DataSet
        Dim cmd As New OleDbCommand
        'Dim cmd As New SqlCommand
        Dim da As New OleDb.OleDbDataAdapter
        'Dim da As New SqlDataAdapter

        Try
            If MyDBCn.State = ConnectionState.Closed Then
                MyOnlineFlg = False
                ErrRecording("", "DBConnection/ExecuteProcedureLOCALSVR", "Can not connect to SQL Local ")
                Return ds
            End If
            cmd.Connection = MyDBCn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = _spName
            da.SelectCommand = cmd
            da.Fill(ds)
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            ErrRecording("", "DBConnection/ExecuteProcedureSQLSVR", ex.Message)
        Finally
            cmd.Dispose()
            da.Dispose()
        End Try
        Return ds
    End Function

    'Public Function ExecuteProcedureDBSQLSVR_1(ByVal _spName As String) As DataTable
    '    'Dim cmd As New SqlCommand
    '    Dim cmd As New OleDbCommand
    '    'Dim MyReader As SqlDataReader = Nothing
    '    Dim MyReader As New OleDb.OleDbDataReader
    '    Dim MyTable As New DataTable
    '    Try
    '        If MyDBCn.State = ConnectionState.Closed Then
    '            MyOnlineFlg = False
    '            ErrRecording("", "DBConnection/ExecuteProcedureLOCALSQLSVR_1", "Can not connect to SQL Local ")
    '            Return MyTable
    '        End If
    '        cmd.Connection = MyDBCn
    '        cmd.CommandType = CommandType.Text
    '        cmd.CommandText = _spName
    '        MyReader = cmd.ExecuteReader()
    '        If MyReader.HasRows Then
    '            For MyIndex As Integer = 0 To MyReader.FieldCount - 1
    '                MyTable.Columns.Add(New DataColumn(MyReader.GetName(MyIndex), MyReader.GetFieldType(MyIndex)))
    '            Next
    '            Dim MyValues(MyReader.FieldCount - 1) As Object
    '            Dim MyRowIndex As Integer = 0
    '            While MyReader.Read()
    '                MyReader.GetValues(MyValues)
    '                MyTable.Rows.Add(MyValues)
    '            End While
    '            MyRowIndex += 1
    '        End If
    '    Catch ex As Exception
    '        'MessageBox.Show(ex.Message)
    '        ErrRecording("", "DBConnection/ExecuteProcedureLOCALSQLSVR_1", ex.Message)
    '    Finally
    '        cmd.Dispose()
    '        MyReader.Close()
    '        MyReader = Nothing
    '    End Try
    '    Return MyTable
    'End Function

    Public Function ExecuteLOCALSVR(ByVal _spName As String) As Boolean
        Dim result As Boolean = False
        Dim cmd As New OleDbCommand
        'Dim cmd As New SqlClient.SqlCommand
        Try
            If MyDBCn.State = ConnectionState.Closed Then
                MyOnlineFlg = False
                ErrRecording("", "DBConnection/ExecuteLOCALSVR", "Can not connect to SQL Local ")
                Return result
            End If

            cmd.Connection = MyDBCn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = _spName
            If cmd.ExecuteNonQuery <> -1 Then
                result = True
            End If
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            ErrRecording("", "DBConnection/ExecuteLOCALSVR", ex.Message)
        Finally
            cmd.Dispose()
        End Try
        Return result
    End Function

    'Public Function GetFormTime(ByVal ID As String) As String
    '    Dim result As String = ""
    '    Dim ds As DataSet
    '    ds = ExecuteProcedureLOCALSVR("SELECT [Outputtime] FROM [Manufacturing] WHERE [ID] = '" & ID & "'")
    '    If ds.Tables(0).Rows.Count <> 0 Then
    '        result = ds.Tables(0).Rows(0)(0)
    '    End If
    '    Return result
    'End Function

    Public Sub Disposing()
        If Not MyDBCn Is Nothing Then
            If MyDBCn.State = ConnectionState.Open Then
                MyDBCn.Close()
            End If
            MyDBCn.Dispose()
        End If
    End Sub
    Public Sub DBClosing()
        If MyDBCn IsNot Nothing Then
            If MyDBCn.State = ConnectionState.Open Then
                MyDBCn.Close()
            End If
        End If
    End Sub

    Public Sub New()
        Try


            'Dim objConn As ADODB.Connection
            'objConn = New ADODB.Connection
            'StrConn_SVR = "Provider=MSDASQL;" & _
            '"Driver={Microsoft dBase Driver (*.dbf)}; DBQ=O:\DBF\MATRAN10.DBF"

            'StrConn_SVR = "Provider={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=O:\DBF;"

            '           objConn.Open()

            'StrConn_SVR = " Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq="O:\DBF\MATRAN10.DBF";"



            '            StrConn_SVR = " Microsoft.Jet.OLEDB.4.0;" + "Data Source = O:\DBF\MATRAN10.DBF;Extended Properties =dBase IV;"

            StrConn_SVR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\RefDatabase\ACCPAC.mdb"
            MyDBCn = New OleDbConnection(StrConn_SVR)
        Catch ex As Exception
            ErrRecording("", "DataConnection/ServerConnecting", ex.Message)

        End Try
    End Sub
End Class
